﻿using Dapper;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Reflection.Emit;
using WebApplication1.Entity;
using yunbo.Model.BIP;

namespace yunbo.Repositorys.FPMRepositorys
{
    public class BD_CPRepository
    {
        public string data = "Data Source=121.37.93.71;Initial Catalog=FanXingMES;User ID=sa;Password=geili@2025;";

        public List<BD_CP> GetBD_CP()
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = "SELECT cp.cp_id,cp.cp_code,cp.cp_name,pp.pp_code,pp.pp_name,cp.cp_fl,d1.dictionary_name as cp_flname,cp.cp_xh,cp.dw_code,dw.dw_name,cp.cp_lx,d2.dictionary_name as cp_lxname,cp.cp_state,cp.bom_code,cp.create_by,cp.create_time,cp.update_by,cp.update_time FROM bd_cp as cp \r\nINNER JOIN  bd_pp as pp ON cp.pp_code = pp.pp_code \r\nINNER JOIN  bd_dw as dw ON cp.dw_code = dw.dw_code \r\nINNER JOIN  dictionary as d1 ON d1.dictionary_id = cp.cp_fl \r\nINNER JOIN  dictionary as d2 ON d2.dictionary_id = cp.cp_lx \r\nwhere is_deleted=1";
                List<BD_CP> bd_cp = connection.Query<BD_CP>(sql).ToList(); // 使用 ToList() 获取所有菜单  
                return bd_cp; 
            }
        }

        /// <summary>
        /// 根据产品编码查询产品
        /// </summary>
        /// <param name="cp_code"></param>
        /// <returns></returns>
        public BD_CP GetBD_CPcp_code(string cp_code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"SELECT cp.cp_id,cp.cp_code,cp.cp_name,pp.pp_code,pp.pp_name,cp.cp_fl,d1.dictionary_name as cp_flname,cp.cp_xh,cp.dw_code,dw.dw_name,cp.cp_lx,d2.dictionary_name as cp_lxname,cp.cp_state,cp.bom_code,cp.create_by,cp.create_time,cp.update_by,cp.update_time FROM bd_cp as cp \r\nINNER JOIN  bd_pp as pp ON cp.pp_code = pp.pp_code \r\nINNER JOIN  bd_dw as dw ON cp.dw_code = dw.dw_code \r\nINNER JOIN  dictionary as d1 ON d1.dictionary_id = cp.cp_fl \r\nINNER JOIN  dictionary as d2 ON d2.dictionary_id = cp.cp_lx where cp_code='{cp_code}  'and is_deleted=1";
                BD_CP bd_cp = connection.Query<BD_CP>(sql).FirstOrDefault(); // 使用 ToList() 获取所有菜单  
                return bd_cp;
            }
        }


        //修改状态
        public int UpdateStatus(string cp_code, int cp_state, int update_by)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                // 使用参数化查询来防止 SQL 注入
                string sql = "UPDATE bd_cp SET cp_state = @cp_state, update_by = @update_by, update_time = GETDATE() WHERE cp_code = @cp_code;";

                // 创建命令对象并设置参数
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@cp_state", cp_state);
                    command.Parameters.AddWithValue("@update_by", update_by);
                    command.Parameters.AddWithValue("@cp_code", cp_code); // 注意：如果 cp_code 是数值类型，则不应该加引号

                    // 打开连接并执行命令
                    connection.Open();
                    int affectedRows = command.ExecuteNonQuery();

                    // 返回受影响的行数
                    return affectedRows;
                }
            }
        }

        /// <summary>
        ///删除 
        /// </summary>
        /// <param name="cp_code"></param>
        /// <param name="is_delete"></param>
        /// <param name="update_by"></param>
        /// <returns></returns>
        public int UpdateDelete(string cp_code, int is_delete, int update_by)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                // 使用参数化查询来防止 SQL 注入
                string sql = "UPDATE bd_cp SET is_deleted = @is_deleted, update_by = @update_by, update_time = GETDATE() WHERE cp_code = @cp_code;";

                // 创建命令对象并设置参数
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@is_deleted", is_delete);
                    command.Parameters.AddWithValue("@update_by", update_by);
                    command.Parameters.AddWithValue("@cp_code", cp_code); // 注意：如果 cp_code 是数值类型，则不应该加引号

                    // 打开连接并执行命令
                    connection.Open();
                    int affectedRows = command.ExecuteNonQuery();

                    // 返回受影响的行数
                    return affectedRows;
                }
            }
        }

        /// <summary>
        /// 修改产品
        /// </summary>
        /// <returns></returns>
        public int UpdateCP(XGBD_CP bD_CP)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                // 使用参数化查询来防止 SQL 注入
                string sql = "UPDATE [dbo].[bd_cp]SET cp_code = @newCpCode,cp_name = @newCpName,pp_code = @newPpCode,cp_fl = @newCpFl,cp_xh = @newCpHx,dw_code = @newDwCode,cp_lx = @newCpLx,bom_code = @newBomCode,update_by = @newUpdateBy,update_time = @newUpdateTime WHERE  cp_code =@newCpCode ;";

                // 创建命令对象并设置参数
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    // 添加参数并绑定到 BD_CP 对象的属性
                    command.Parameters.AddWithValue("@newCpCode", bD_CP.cp_code);
                    command.Parameters.AddWithValue("@newCpName", bD_CP.cp_name);
                    command.Parameters.AddWithValue("@newPpCode", bD_CP.pp_code);
                    command.Parameters.AddWithValue("@newCpFl", bD_CP.cp_fl);
                    command.Parameters.AddWithValue("@newCpHx", bD_CP.cp_xh);
                    command.Parameters.AddWithValue("@newDwCode", bD_CP.dw_code);
                    command.Parameters.AddWithValue("@newCpLx", bD_CP.cp_lx);
                    command.Parameters.AddWithValue("@newBomCode", bD_CP.bom_code);
                    command.Parameters.AddWithValue("@newUpdateBy", bD_CP.update_by);
                    command.Parameters.AddWithValue("@newUpdateTime", bD_CP.update_time);


                    // 打开连接并执行命令
                    connection.Open();
                    int affectedRows = command.ExecuteNonQuery();

                    // 返回受影响的行数
                    return affectedRows;
                }
            }
        }

        /// <summary>
        /// 新增产品
        /// </summary>
        /// <returns></returns>
        public int InsertCP(XGBD_CP bD_CP)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                // 使用参数化查询来防止 SQL 注入
                string sql = "INSERT INTO [dbo].[bd_cp] (cp_code, cp_name, pp_code, cp_fl, cp_xh, dw_code, cp_lx, bom_code, update_by, update_time,is_deleted,cp_state) " +
                             "VALUES (@newCpCode, @newCpName, @newPpCode, @newCpFl, @newCpHx, @newDwCode, @newCpLx, @newBomCode, @newUpdateBy, @newUpdateTime,1,1);";

                // 创建命令对象并设置参数
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    // 添加参数并绑定到 BD_CP 对象的属性
                    command.Parameters.AddWithValue("@newCpCode", bD_CP.cp_code);
                    command.Parameters.AddWithValue("@newCpName", bD_CP.cp_name);
                    command.Parameters.AddWithValue("@newPpCode", bD_CP.pp_code);
                    command.Parameters.AddWithValue("@newCpFl", bD_CP.cp_fl);
                    command.Parameters.AddWithValue("@newCpHx", bD_CP.cp_xh);
                    command.Parameters.AddWithValue("@newDwCode", bD_CP.dw_code);
                    command.Parameters.AddWithValue("@newCpLx", bD_CP.cp_lx);
                    command.Parameters.AddWithValue("@newBomCode", bD_CP.bom_code);
                    command.Parameters.AddWithValue("@newUpdateBy", bD_CP.update_by);
                    command.Parameters.AddWithValue("@newUpdateTime", bD_CP.update_time);

                    // 打开连接并执行命令
                    connection.Open();
                    int affectedRows = command.ExecuteNonQuery();

                    // 返回受影响的行数
                    return affectedRows;
                }
            }
        }


		/// <summary>
		/// 产品列表弹窗
		/// </summary>
		/// <param name="codes"></param>
		/// <returns></returns>
		public List<BD_CP> GetCPs(string? codes)
		{
			List<BD_CP> cp = new List<BD_CP>();

			if (string.IsNullOrEmpty(codes))
			{
				return cp;
			}

			// 清理和分割codes，移除空项和空格
			var codeList = codes.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries)
								.Select(s => s.Trim())
								.Where(s => !string.IsNullOrEmpty(s))
								.ToList();

			if (codeList.Count == 0)
			{
				return cp;
			}

			using (SqlConnection connection = new SqlConnection(data))
			{
				// 基础SQL查询
				string sql = @"SELECT [cp_id], [cp_code], [cp_name],
            (SELECT dictionary_name FROM dictionary WHERE dictionary_id=[cp_fl]) cp_flname,
            [cp_xh] 
        FROM [dbo].[bd_cp] 
        WHERE [cp_state]=1 AND [is_deleted]=1 ";

				// 判断是否需要添加IN条件（当codes非空且不等于"0"时）
				bool shouldAddInClause = codeList.Count > 0 && !(codeList.Count == 1 && codeList[0] == "0");

				if (shouldAddInClause)
				{
					var parameters = Enumerable.Range(0, codeList.Count).Select(i => $"@code{i}").ToArray();
					sql += " AND [cp_code] IN (" + string.Join(", ", parameters) + ")";
				}

				connection.Open();

				using (SqlCommand command = new SqlCommand(sql, connection))
				{
					if (shouldAddInClause)
					{
						// 绑定参数（字符串类型）
						for (int i = 0; i < codeList.Count; i++)
						{
							command.Parameters.AddWithValue($"@code{i}", codeList[i]);
						}
					}

					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{
							cp.Add(new BD_CP
							{
								cp_id = Convert.ToInt32(reader["cp_id"]),
								cp_code = reader["cp_code"].ToString(),
								cp_name = reader["cp_name"].ToString(),
								cp_flname = reader["cp_flname"].ToString(),
								cp_xh = reader["cp_xh"].ToString()
							});
						}
					}
				}
			}

			return cp;
		}
	}
}
